package com.example.demo;

import com.alibaba.fastjson.JSONObject;
import org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

public class Students extends JFrame {
    Connection connection;

    public Students() throws SQLException {
        connection = DriverManager.getConnection("jdbc:mysql://192.168.198.128:3306/recommend_system?characterEncoding=UTF-8",
                "root", "518985599.");
        setSize(300, 400);
        setLocation(200, 200);
        setLocationRelativeTo(null);
        JPanel jPanel = createPanel();
        JButton b = new JButton("插入");
        b.setBounds(150, 120 + 30, 80, 30);
        JButton select = new JButton("查询");
        select.setBounds(150, 120 + 30, 80, 30);
        JButton update = new JButton("更新");
        update.setBounds(150, 120 + 30, 80, 30);
        jPanel.add(b);


        JTextField info2 = new JTextField("");
        jPanel.add(info2);
        JPanel jPanel2 = new JPanel(new GridLayout(4, 2));
        JLabel lNumber = new JLabel("学号");
        JTextField tfNumber = new JTextField("");
        jPanel2.add(lNumber);
        jPanel2.add(tfNumber);
        jPanel2.add(select);
        JTextArea ta = new JTextArea();
        ta.setLineWrap(true);
        JScrollPane sp = new JScrollPane(ta);
        jPanel2.add(sp);

        JLabel lNumber2 = new JLabel("要修改的学生学号");
        JTextField tfNumber2 = new JTextField("");
        JPanel jPanel3 = createPanel();
        jPanel3.add(lNumber2);
        jPanel3.add(tfNumber2);
        jPanel3.add(update);
        JTextField info = new JTextField("");
        jPanel3.add(info);
        JTabbedPane tabbedPane = new JTabbedPane();
        tabbedPane.addTab("插入", jPanel);
        tabbedPane.addTab("查询", jPanel2);
        tabbedPane.addTab("更新", jPanel3);
        tabbedPane.setSelectedIndex(1);
        setContentPane(tabbedPane);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setVisible(true);


        update.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String key_value = "";
                JTextField tfName = (JTextField) jPanel3.getComponent(1);
                JTextField tfAge = (JTextField) jPanel3.getComponent(3);
                JTextField tfLocation = (JTextField) jPanel3.getComponent(5);
                JTextField tfNation = (JTextField) jPanel3.getComponent(7);
                JTextField tfNumber = (JTextField) jPanel3.getComponent(9);
                String name = tfName.getText();
                String birthday = tfAge.getText();
                String number = tfNumber.getText();
                String location = tfLocation.getText();
                String nation = tfNation.getText();
                String number2 = tfNumber2.getText();
                key_value = concat("name", name) + key_value;
                key_value = concat("birthday", birthday) + key_value;
                key_value = concat("nation", nation) + key_value;
                key_value = concat("number", number) + key_value;
                key_value = concat("location", location) + key_value;
                key_value = key_value.substring(0, key_value.length() - 1) + " ";
                String sql = "update tmp_students set " + key_value + "where number ='" + number2 + "'";
                try {
                    Statement statement = connection.createStatement();
                    int r = statement.executeUpdate(sql);
                    if (r >= 0) {
                        info.setText("修改成功");
                    }
                    System.out.println(r);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }

            }
        });


        select.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                JSONObject jsonObj = new JSONObject();
                ta.setText("");
                int number = Integer.parseInt(tfNumber.getText());
                String sql = "select * from tmp_students where number=?";
                try {
                    PreparedStatement preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setInt(1, number);
                    ResultSet r = preparedStatement.executeQuery();
                    ResultSetMetaData metaData = r.getMetaData();
                    while (r.next()) {
                        for (int i = 1; i < metaData.getColumnCount(); i++) {
                            String columnName = metaData.getColumnName(i);
                            String value = r.getString(columnName);
                            jsonObj.put(columnName, value);
                        }
                    }
                    ta.append(jsonObj.toString());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        });


        b.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                JTextField tfName = (JTextField) jPanel.getComponent(1);
                JTextField tfAge = (JTextField) jPanel.getComponent(3);
                JTextField tfLocation = (JTextField) jPanel.getComponent(5);
                JTextField tfNation = (JTextField) jPanel.getComponent(7);
                JTextField tfNumber = (JTextField) jPanel.getComponent(9);
                String name = tfName.getText();
                String birthday = tfAge.getText();
                int number = Integer.parseInt(tfNumber.getText());
                String location = tfLocation.getText();
                String nation = tfNation.getText();
                PreparedStatement ps = null;
                try {
                    String sql = "insert into tmp_students values(null,?,?,?,?,?)";
                    ps = connection.prepareStatement(sql);
                    System.out.println(name + birthday + nation + number + location);
                    ps.setString(1, name);
                    ps.setString(2, birthday);
                    ps.setString(3, nation);
                    ps.setInt(4, number);
                    ps.setString(5, location);
                    int res = ps.executeUpdate();
                    System.out.println(res);
                    if (res > 0) {
                        info2.setText("插入成功");
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                } finally {
                    try {
                        ps.close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            }
        });
    }

    public JPanel createPanel() {
        JLabel lName = new JLabel("姓名");
        JTextField tfName = new JTextField("");
        JLabel lAge = new JLabel("出生日期");
        JTextField tfAge = new JTextField("");
        JLabel lNation = new JLabel("民族");
        JTextField tfNation = new JTextField("");
        JLabel lNumber = new JLabel("学号");
        JTextField tfNumber = new JTextField("");
        JLabel lLocation = new JLabel("住址");
        JTextField tfLocation = new JTextField("");
        JPanel jPanel = new JPanel(new GridLayout(7, 2));
        jPanel.add(lName);
        jPanel.add(tfName);
        jPanel.add(lAge);
        jPanel.add(tfAge);
        jPanel.add(lLocation);
        jPanel.add(tfLocation);
        jPanel.add(lNation);
        jPanel.add(tfNation);
        jPanel.add(lNumber);
        jPanel.add(tfNumber);

        return jPanel;
    }

    public String concat(String key, String value) {
        if (!value.equals("")) {
            return key + "='" + value + "',";
        }
        return "";
    }

    public static void main(String[] args) throws SQLException {
        Students students = new Students();
    }
}
